#!/usr/bin/python
# -*- coding: utf-8 -*-
#
# FOR WIN - C:\Python27\Python.exe


# Locsi Data Base interfacing functions based on
# Google Calendar API v3 Resource representations and interfacing functions

#--------------------------------
# LocsiDB API
#--------------------------------
#
# Assumption: sqlite db file exists 
#
# Global variables
#   LocsiDB.dbpath - path of the sqlite db file
#   LocsiDB.dbtable - name of the event table in the db
#
# Functions
#   initdb( dbpath, dbtable, droptable = 0 ) 
#       returns with a connection to the db table
#       dbpath/dbtable     
#       if droptable = 1 drops table in db if exists
#
#   initdbfromGC( ldbpath=LocsiDB.dbpath,ldbtable=LocsiDB.dbtable )
#       returns with a connection to the db table
#       dbpath/dbtable initialized from Google Calendar
#       using LocsiGoogleCalendar_v3 API
#
#   closedb(con)
#       closes the connection with the DB
#
#   getactions(con)
#       prints selected attributes of current actions
#       (sample code for reading from DB)


import LocsiGoogleCalendar_v3 as calapi
import sqlite3 as lite
import sys
import urllib
import time
import datetime

dbpath='/home/pi/locsolo/Locsi/Locsi.db'

dbtable='Events'

def getfields(resourcedef):
    fieldlist = []

    for t,n in resourcedef.iteritems():
        fieldlist.append(t)
        
    return fieldlist

def gensqlcreate(fieldlist, table):
    sqlcreate = 'CREATE TABLE %s ( PrimaryId INTEGER PRIMARY KEY, Touched INTEGER, Done INTEGER, Progress INTEGER, GCUpdate TXT' %table

    for element in fieldlist:
        sqlcreate = sqlcreate + ', '+ element + ' TXT'

    sqlcreate = sqlcreate + ');'
    
    return sqlcreate

def initdb(dbpath, table, droptable = 0):

    con = None

    try:
        con = lite.connect(dbpath)
        cur = con.cursor()
        if droptable == 1 :
            cur.execute("DROP TABLE IF EXISTS %s;" %table)
            #print "DROP Table %s" %table
            # print gensqlcreate(getfields(calapi.LocsiCFG.events_resource),table)
            cur.execute(gensqlcreate(getfields(calapi.LocsiCFG.events_resource),table))
        
    except lite.Error, e:

        print "Error connecting to DB %s:" %e.args[0]
        #sys.exit(1)
        raise

    finally:
        if con:
            return con

def closedb(con):
    if con:
        con.close()
        
def encodefordb(x):
    return urllib.quote_plus(repr(x))

def decodefromdb(x):
    return eval(urllib.unquote_plus(x))

def getactions(con):
    global dbpaht, dbtable
    con.row_factory = lite.Row
    cur = con.cursor()
    print "SELECT RESULT\n"
    cur.execute("SELECT * FROM %s;"%dbtable)
    rows = cur.fetchall()
    for row in rows:
        
        #print str(row)
        #print "%s\n" %row["id"]
        #print "%s\n" %row["Touched"]
        #print "%s\n" %row["Done"]

        id = row["id"]

        #sqlupdate = 'UPDATE %s SET Progress = 1 WHERE id ="'%dbtable+id+'"'
        #print sqlupdate
        #cur.execute(sqlupdate)
        #con.commit()

        #x = decodefromdb(row["start"])
        #y = decodefromdb(row["end"])
        #print x["dateTime"]
        #print type(x)

        eventStart = str(decodefromdb(row["start"])).split('\'')[3]
        eventEnd = str(decodefromdb(row["end"])).split('\'')[3]

        dt_eventStart = datetime.datetime.strptime(eventStart[:-6],"%Y-%m-%dT%H:%M:%S")
        dt_eventEnd = datetime.datetime.strptime(eventEnd[:-6],"%Y-%m-%dT%H:%M:%S")
        dt_duration = dt_eventEnd - dt_eventStart

        print 'EventStart:',dt_eventStart,dt_eventEnd,dt_duration
        
        x = decodefromdb(row["description"])
        print x
        print type(x)

def initdbfromGC(hoursm = 0, hoursp = 168, ldbpath=None, ldbtable=None):
    global dbpath, dbtable

    if ldbpath != None:
        dbpath = ldbpath

    if ldbtable != None:
        dbtable = ldbtable

    print "Locsi DB path: %s" %dbpath
    print "Locsi DB table: %s" %dbtable

    con = None
    con = initdb(dbpath, dbtable,1)
    cur = con.cursor()
    cur.execute('SELECT SQLITE_VERSION()')
    
    data = cur.fetchone()
    
    # print "SQLite version: %s" % data
    
    service = calapi.authenticategooglecalendarapi()
    # events = calapi.geteventsfromcalendar( service, 'primary' )

    events = calapi.geteventsfromcalendarintimewindow( service, 'primary', hoursm, hoursp )
    #print events
    #print type(events)

    print "GENERATE INSERT"
    for event in events:
        updatedsummary = event['summary'] + ' DOWNLOADED'
        event['colorId'] = '6'
        event['summary']
        sqlinsert1 = 'INSERT INTO %s(Progress, Touched, Done, GCUpdate, ' %dbtable
        sqlinsert2 = "VALUES (0, 0, 0, '',"
        for c, v in event.iteritems():
            sqlinsert1 = sqlinsert1 + c + ', '
            sqlinsert2 = sqlinsert2 + "'"+ encodefordb(v)+"'" + ', '

        sqlinsert = sqlinsert1[:-2] + ') ' + sqlinsert2[:-2] + ');'

        print sqlinsert

        cur.execute( sqlinsert )

    con.commit()


    con.row_factory = lite.Row
    cur = con.cursor()
    print "SELECT RESULT"
    cur.execute("SELECT * FROM %s;"%dbtable)
    rows = cur.fetchall()
    for row in rows:

        #print "%s\n" %row["id"]
         
        x = decodefromdb(row["id"])
        print "Event id: %s " %x 
        
        #print "%s\n" %row["start"]
         
        x = decodefromdb(row["start"])
        print "Event start: %s " %x
        #print type(x)
        
    #closedb(con)
    print "Locsi DB Initialized X"
    return con

#if __name__ == "__main__":
#    import sys
#    run()
#    LocsiDB(sys.argv[1],sys.argv[2])
